﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using MySql.Data.MySqlClient;
using CS2102_Music.Logic.LogicModels;

namespace CS2102_Music.Logic
{
    public partial class Logic
    {
        // Functions with SQL
        public bool CheckUserWishedSong(string user, int wishProduct)
        {
            string query = "SELECT COUNT(*) FROM song_wish_to_own sw WHERE sw.wisher = @user AND sw.song = @songID";
            int count = 0;
            bool wished = false;

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query
                                                                            , new QueryParameter("@user", user)
                                                                            , new QueryParameter("@songID", wishProduct.ToString())
                                                                            );
            while (result.Read())
            {
                count = result.GetInt32(0);
            }

            result.Close();

            if (count == 0)
                wished = false;
            else
                wished = true;

            return wished;
        }
        public bool CheckUserWishedAlbum(string user, int wishProduct)
        {
            string query = "SELECT COUNT(*) FROM album_wish_to_own aw WHERE aw.wisher = @user AND aw.album = @albumID";
            int count = 0;
            bool wished = false;

            MySqlDataReader result = databaseConnector.ExecuteReaderQuery(query
                                                                            , new QueryParameter("@user", user)
                                                                            , new QueryParameter("@albumID", wishProduct.ToString())
                                                                            );
            while (result.Read())
            {
                count = result.GetInt32(0);
            }

            result.Close();

            if (count == 0)
                wished = false;
            else
                wished = true;

            return wished;
        }
        public void AddSongToWishList(string user, int wishProduct)
        {
            string query = "INSERT INTO song_wish_to_own VALUES (@user, @songID)";

            databaseConnector.ExecuteNonQuery(query
                                            , new QueryParameter("@user", user)
                                            , new QueryParameter("@songID", wishProduct.ToString())
                                        );

        }
        public void AddAlbumToWishList(string user, int wishProduct)
        {
            string query = "INSERT INTO album_wish_to_own VALUES (@user, @albumID)";

            databaseConnector.ExecuteNonQuery(query
                                            , new QueryParameter("@user", user)
                                            , new QueryParameter("@albumID", wishProduct.ToString())
                                        );

        }
        public void RemoveSongFromWish(string user, int wishProduct)
        {
            string query = "DELETE FROM song_wish_to_own WHERE wisher = @user AND song = @songID";

            databaseConnector.ExecuteNonQuery(query
                                            , new QueryParameter("@user", user)
                                            , new QueryParameter("@songID", wishProduct.ToString())
                                        );
        }
        public void RemoveAlbumFromWish(string user, int wishProduct)
        {
            string query = "DELETE FROM album_wish_to_own WHERE wisher = @user AND album = @albumID";

            databaseConnector.ExecuteNonQuery(query
                                            , new QueryParameter("@user", user)
                                            , new QueryParameter("@albumID", wishProduct.ToString())
                                        );
        }
        public WishListL GetWishListByUser(string user)
        {
            WishListL wish = new WishListL();
            SongL song = new SongL();
            AlbumL album = new AlbumL();

            // songs
            string query = "SELECT * FROM song_wish_to_own swo, song_owned_by sob WHERE swo.song = sob.songID AND swo.wisher = @user AND sob.active = 'Y' ORDER BY sob.title";
            MySqlDataReader result1 = databaseConnector.ExecuteReaderQuery(query
                                                                            , new QueryParameter("@user", user)
                                                                            );
            while (result1.Read())
            {
                song.ID = result1.GetInt32(1);
                song.Title = result1.GetString(3);
                song.ReleaseDate = result1.GetDateTime(4);
                song.Price = Math.Round((result1.GetDouble(5)),2);
                song.Genre = result1.GetString(6);
                song.Composer = result1.GetString(7);
                song.Lyricist = result1.GetString(8);
                song.Duration = result1.GetInt32(9);
                song.Active = result1.GetString(10);

                wish.WishedSongs.Add(song);

                song = new SongL();
            }

            result1.Close();

            int songID;

            for (int i = 0; i < wish.WishedSongs.Count(); i++)  // get artists for each song
            {
                songID = wish.WishedSongs[i].ID;

                string query2 = "SELECT * FROM artist a, sing s WHERE a.artistID = s.artist AND s.song = @songID";
                ArtistL artist = new ArtistL();

                MySqlDataReader result2 = databaseConnector.ExecuteReaderQuery(query2
                                                                                , new QueryParameter("@songID", songID.ToString())
                                                                                );

                while (result2.Read())
                {
                    artist.Name = result2.GetString(0);
                    artist.Photo = result2.GetString(1);
                    artist.ID = result2.GetInt32(2);

                    wish.WishedSongs[i].Artists.Add(artist);

                    artist = new ArtistL();
                }

                result2.Close();
            }

            // albums
            query = "SELECT * FROM album_wish_to_own awo, album_owned_by aob WHERE awo.album = aob.albumID AND awo.wisher = @user AND aob.active = 'Y' ORDER BY aob.title";
            MySqlDataReader result3 = databaseConnector.ExecuteReaderQuery(query
                                                                           , new QueryParameter("@user", user)
                                                                           );
            while (result3.Read())
            {
                album.ID = result3.GetInt32(1);
                album.Title = result3.GetString(2);
                album.ReleaseDate = result3.GetDateTime(3);
                album.CoverPhoto = result3.GetString(4);
                album.Price = Math.Round((result3.GetDouble(5)),2);
                album.Active = result3.GetString(7);

                wish.WishedAlbums.Add(album);

                album = new AlbumL();
            }

            result3.Close();

            return wish;
        }

    }
}